﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.GERP.GHRMS.DALFactory;
using KPIS.DBM;


namespace KPIS.GERP.GHRMS.BLL
{
    public class HR_Development
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataSet ListSearchRequest(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "SELECT tr.requested_when AS requested_when"
					+ ",CONCAT(np.PREFIX_NAME, v.FIRST_NAME, ' ', v.LAST_NAME) AS EMP_NAME"
					+ ",j.job_site_name AS JOB_SITE_DESCR"
					+ ",tr.training_cur_descr"
					+ ",tr.date_training AS DATE_TRAINING"
					+ ",tr.training_place"
					+ ",tr.training_create"
					+ ",tr.remarks"
					+ ",CASE WHEN tr.status = 'A' THEN 'รับเรื่อง' ELSE 'รอตรวจสอบ' END AS STATUS"
					+ ",tr.request_seq"
				    + "FROM hrm_training_request tr inner join hrm_ps_employees v ON tr.emp_seq = v.emp_seq"
					+ "inner join hrm_mp_job_sites j on v.working_job_site_seq = j.job_site_seq"
					+ "left join MAS_NAME_PREFIXES  np ON v.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + "WHERE  tr.record_status not like 'D' ";
            if (seq != "")
            {
                sql += " and request_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public DataSet ListTrainingCourses(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "SELECT * "
                       + " FROM hrm_training_courses"
                       + " where record_status not like 'D'";
            if (seq != "")
            {
                sql += " and training_ins_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
        public void DeleteTrainingCourses(int Sequence)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", UpdateBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_courses SET RECORD_STATUS=@record_status WHERE tran_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public void InsertTrainingCourses
            ( Nullable<int> training_cur_seq
            , string ref_no
            , string training_course_descr
            , Nullable<int> training_ins_seq
            , Nullable<int> scholar_fund_seq
            , Nullable<int> bg_source_seq
            , Nullable<int> training_group_seq
            , int epc_seq
            , DateTime date_sign_start
            , DateTime date_sign_end
            , DateTime date_training_start
            , DateTime date_training_end
            , Nullable<int> max_trainee_count
            , Nullable<int> unit_price
            , string schedule_text
            , Nullable<int> country_seq
            , string remarks
            , int closed
            , Nullable<int> total_score
            , Nullable<int> score_very_good
            , Nullable<int> score_good
            , Nullable<int> score_acceptable
            , int score_type
            , int created_by
            , DateTime created_when )
        {
            string sql;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(26);

                dbManager.AddParameters(0, "@CreatedBy", created_by);
                dbManager.AddParameters(1, "@training_cur_seq", training_cur_seq);
                dbManager.AddParameters(2, "@ref_no", ref_no);
                dbManager.AddParameters(3, "@training_course_descr", training_course_descr);
                dbManager.AddParameters(4, "@unit_price", unit_price);
                dbManager.AddParameters(5, "@training_ins_seq", training_ins_seq);
                dbManager.AddParameters(6, "@scholar_fund_seq", scholar_fund_seq);
                dbManager.AddParameters(7, "@training_group_seq", training_group_seq);
                dbManager.AddParameters(8, "@epc_seq", epc_seq);
                dbManager.AddParameters(9, "@date_sign_start", date_sign_start);
                dbManager.AddParameters(10, "@date_sign_end", date_sign_end);
                dbManager.AddParameters(11, "@date_training_start", date_training_start);
                dbManager.AddParameters(12, "@date_training_end", date_training_end);
                dbManager.AddParameters(13, "@bg_source_seq", bg_source_seq);
                dbManager.AddParameters(14, "@schedule_text", schedule_text);
                dbManager.AddParameters(15, "@max_trainee_count", max_trainee_count);
                dbManager.AddParameters(16, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(17, "@closed", closed);
                dbManager.AddParameters(18, "@total_score", total_score);
                dbManager.AddParameters(19, "@score_very_good", score_very_good);
                dbManager.AddParameters(20, "@score_good", score_good);
                dbManager.AddParameters(21, "@score_acceptable", score_acceptable);
                dbManager.AddParameters(22, "@score_type", score_type);
                dbManager.AddParameters(23, "@remarks", remarks);
                dbManager.AddParameters(24, "@country_seq", country_seq);
                dbManager.AddParameters(25, "@created_when", created_when);

                sql = "INSERT INTO hrm_training_courses"
                    + " (ref_no"
                    + ", training_course_descr"
                    + ", training_cur_seq"
                    + ", date_sign_start"
                    + ", date_sign_end"
                    + ", date_training_start"
                    + ", date_training_end"
                    + ", schedule_text"
                    + ", max_trainee_count"
                    + ", unit_price"
                    + ", total_score"
                    + ", score_very_good"
                    + ", score_good"
                    + ", score_acceptable"
                    + ", score_type"                    
                    + ", closed"
                    + ", scholar_fund_seq"
                    + ", epc_seq"
                    + ", training_ins_seq"
                    + ", remarks"
                    + ", country_seq"
                    + ", training_group_seq"
                    + ", bg_source_seq"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when)"
                    + " VALUES (@ref_no"
                    + ", @training_course_descr"
                    + ", @training_cur_seq"
                    + ", @date_sign_start"
                    + ", @date_sign_end"
                    + ", @date_training_start"
                    + ", @date_training_end"
                    + ", @schedule_text"
                    + ", @max_trainee_count"
                    + ", @unit_price"
                    + ", @total_score"
                    + ", @score_very_good"
                    + ", @score_good"
                    + ", @score_acceptable"
                    + ", @score_type" 
                    + ", @closed"
                    + ", @scholar_fund_seq"
                    + ", @epc_seq"
                    + ", @training_ins_seq"
                    + ", @remarks"
                    + ", @country_seq"
                    + ", @training_group_seq"
                    + ", @bg_source_seq"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataSet ListTrainingCoursesDescr(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = " SELECT  tran_seq as Sequence, tCou.ref_no, tCou.training_course_descr"
						+ ", tCou.date_training_start AS FMT_DATE_TRAINING_START"
						+ ", tCou.date_training_end AS FMT_DATE_TRAINING_END"
						+ ", tCou.schedule_text, tCou.tran_seq, tCou.date_training_start, tCou.date_training_end"
						+ ", (select count(seq_no) from hrm_training_courses_trainees where tran_seq = tCou.tran_seq) AS COUNT_TRAINEE"
						+ ", (select count(seq_no) from hrm_training_courses_trainees where tran_seq = tCou.tran_seq and granted = 1) AS COUNT_TRAINEE_GRANTED"
				  + " FROM hrm_training_courses as tCou "
                       + " where tCou.record_status not like 'D'";
            if (seq != "")
            {
                sql += " and tran_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public void DeleteTrainingCoursesDescr(int Sequence)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", UpdateBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_courses SET RECORD_STATUS=@record_status WHERE tran_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public DataSet ListTrainingCoursesDescra(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = " SELECT  tran_seq as Sequence, tCou.ref_no, tCou.training_course_descr"
                        + ", tCou.date_training_start AS FMT_DATE_TRAINING_START"
                        + ", tCou.date_training_end AS FMT_DATE_TRAINING_END"
                        + ", tCou.schedule_text, tCou.tran_seq, tCou.date_training_start, tCou.date_training_end"
                        + ", (select count(seq_no) from hrm_training_courses_trainees where tran_seq = tCou.tran_seq) AS COUNT_TRAINEE"
                        + ", (select count(seq_no) from hrm_training_courses_trainees where tran_seq = tCou.tran_seq and granted = 1) AS COUNT_TRAINEE_GRANTED"
                  + " FROM hrm_training_courses as tCou "
                       + " where tCou.record_status not like 'D'";
            if (seq != "")
            {
                sql += " and tran_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
    }
}
